<?php
include_once XXF_EXCEL;
class Pxy_down_ctl extends Ctl{
    function __construct(){
    }
    function main(array $xxfget) {
        $this::sessver();
        $type=!empty($xxfget['type'])?$xxfget['type']:false;
        if(!$type){
        	$this::moren($xxfget);
        }elseif($type=="1"){//下载考核登记表
            $this::down1($xxfget);
        }elseif($type=="2"){//下载班级档案表
            $this::down2($xxfget);
        }
    }
    
    function down2xinxun($pid,$pxbrow){
        $pxyarr=xdb::getInstance()->getarray("select * from peixunxueyuan where pid=".$pid);
        $pinfo=xdb::getInstance()->getarray("select *,count(1) as knum from peixun_jk as jk,peixunkecheng as k,peixunjiaoshi as j where jk.pjid=j.id and jk.pkid=k.id and k.ktype like '普通课' and pid=".$pid." GROUP BY kname");
        //
        header("Content-Type:text/html;charset=utf-8");error_reporting(E_ALL);ini_set('display_errors', TRUE);ini_set('display_startup_errors', TRUE);
        $e5=new PHPExcel_Reader_Excel5();
        $excel=$e5->load('uploader/moban/BanJiDangAnBiaoxinxun.xls');
        //第一张表单设置
        $es0=$excel->getSheet(0);
        $es0->setCellValue('B3',$pxbrow['pname']);
        $es0->setCellValue('B4',count($pxyarr).'人');
        $es0->setCellValue('B5',$pxbrow['stime'].'至'.$pxbrow['etime']);
        //第二张表单设置
        $es1=$excel->getSheet(1);
        $es1->setCellValue('B3',$pxbrow['pname']);
        $es1->setCellValue('B4',count($pxyarr).'人');
        $es1->setCellValue('B5',$pxbrow['stime'].'至'.$pxbrow['etime']);
        //第四张表单设置
        $es3=$excel->getSheet(3);
        $es3->setCellValue('A2',"培训学校：（公章）   培训班名称：".$pxbrow['pname']."     起止时间：".$pxbrow['stime'].'至'.$pxbrow['etime']);
        $i=0;
        foreach ($pxyarr as $row){
            $es3->setCellValue('A'.($i+4),$i+1);
            $es3->setCellValue('B'.($i+4),$row['idcard']);
            $es3->setCellValue('C'.($i+4),$row['puname']);
            $es3->setCellValue('D'.($i+4),_getSexFromIdcard($row['idcard']));
            $es3->setCellValue('E'.($i+4),substr(_getShengRiFromIdcard($row['idcard']),0,6)." ");
            $es3->setCellValue('F'.($i+4),$row['zhiwu']);
            $es3->setCellValue('G'.($i+4),($row['zhicheng']=="无"?"":$row['zhicheng']));
            $es3->setCellValue('H'.($i+4),$row['zhuanye']);
            $es3->setCellValue('I'.($i+4),$row['mphone']);
            $es3->setCellValue('J'.($i+4),$row['wenhua']);
            $es3->setCellValue('K'.($i+4),$row['workunit']);
            $i++;
        }
        $es3->getStyle('A4:L'.($i+3))->applyFromArray(//为新增的学员行添加边框
            array(
                'borders'=>array(
                    'allborders'=>array(
                        'style'=>PHPExcel_Style_Border::BORDER_THIN
                    )
                )
            )
            );
        //第五章表的操作
        $es4=$excel->getSheet(4);
        $es4->setCellValue('A2',"培训学校：（公章）    培训班名称：".$pxbrow['pname']."    起止时间：".$pxbrow['stime'].'至'.$pxbrow['etime']);
        $i=0;
        foreach ($pinfo as $row){$i++;
            $es4->setCellValue('A'.($i+4),$i);
            $es4->setCellValue('B'.($i+4),$row['kname']);
            $es4->setCellValue('C'.($i+4),intval($row['knum'])*2);
            $es4->setCellValue('D'.($i+4),$row['jiaocai']);
            $es4->setCellValue('E'.($i+4),$row['tname']);
            $es4->setCellValue('F'.($i+4),$row['workunit']);
            $es4->setCellValue('G'.($i+4),$row['biyeschool']);
            $es4->setCellValue('H'.($i+4),$row['zhuanye']);
            $es4->setCellValue('I'.($i+4),$row['zhicheng']);
            $es4->getRowDimension($i+4)->setRowHeight(28);
        }
        $es4->getStyle('A5:J'.($i+4))->applyFromArray(//添加边框
            array(
                'borders'=>array(
                    'allborders'=>array(
                        'style'=>PHPExcel_Style_Border::BORDER_THIN
                    )
                )
            )
            );
        //第六张表的操作考试成绩
        $es5=$excel->getSheet(5);
        $es5->setCellValue('A2',"培训学校：（公章）    培训班名称：".$pxbrow['pname']."    起止时间：".$pxbrow['stime'].'至'.$pxbrow['etime']);
        $i=0;
        foreach ($pxyarr as $row){$i++;
            $es5->setCellValue('A'.($i+3),$i);
            $es5->setCellValue('B'.($i+3),$row['puname']);
            $es5->setCellValue('C'.($i+3),$row['chengji']);
            $es5->setCellValue('D'.($i+3),$row['bukaochengji']);
            $es5->setCellValue('E'.($i+3),$row['beizhu']=="无"?"":$row['beizhu']);
        }
        $es5->getStyle('A3:E'.($i+3))->applyFromArray(//添加边框
            array(
                'borders'=>array(
                    'allborders'=>array(
                        'style'=>PHPExcel_Style_Border::BORDER_THIN
                    )
                )
            )
            );
        //第七张的操作报审表1
        $es6=$excel->getSheet(6);
        $zongkeshi=0;
        $i=0;
        foreach ($pinfo as $row){$i++;
            //$zongkeshi+=intval($row['keshi']);
            $es6->setCellValue('I'.($i+3),$row['riqi']);
            $es6->setCellValue('J'.($i+3),$row['kname']);
            $es6->setCellValue('K'.($i+3),intval($row['knum'])*2);
            $es6->setCellValue('L'.($i+3),$row['tname']);
            $zongkeshi+=intval($row['knum'])*2;
        }
        $es6->setCellValue('B2',$pxbrow['pname']);
        $es6->setCellValue('E2',xdb::getInstance()->getvar("select realname from user where id=".$pxbrow['bzrid']));
        $es6->setCellValue('B3',$pxbrow['peixunaddress']);
        $es6->setCellValue('D3',$pxbrow['dianhua']);
        $es6->setCellValue('F3',$pxbrow['youbian']);
        $es6->setCellValue('B4',$pxbrow['stime']."至".$pxbrow['etime']);
        $es6->setCellValue('B5',$zongkeshi."学时");
        $es6->setCellValue('E5',$zongkeshi."学时");
        $es6->setCellValue('G5',count($pxyarr));
        $es6->setCellValue('B7',$pxbrow['etime']);
        $es6->setCellValue('D9',$pxbrow['lilunaddress']);
        $es6->setCellValue('K17',$zongkeshi);
        //第八张表操作 报审表2
        $es7=$excel->getSheet(7);
        $ts=xdb::getInstance()->getarray("select DISTINCT tname,sex,shengri,wenhua,zhuanyeage,zhicheng,jiaoxueage,workunit from peixun_jk as jk,peixunkecheng as k,peixunjiaoshi as j where jk.pjid=j.id and jk.pkid=k.id and k.ktype like '普通课' and pid=".$pid);
        $i=0;
        foreach ($ts as $row){
            $es7->setCellValue('A'.($i+3),$row['tname']);
            $es7->setCellValue('B'.($i+3),$row['sex']);
            $es7->setCellValue('C'.($i+3),$row['shengri']);
            $es7->setCellValue('D'.($i+3),$row['wenhua']);
            $es7->setCellValue('E'.($i+3),$row['zhicheng']);
            $es7->setCellValue('F'.($i+3),$row['workunit']);
            $i++;
        }
        $es7->setCellValue('J19',$pxbrow['pname']);
        //第九张表操作 验印审批表
        $es8=$excel->getSheet(8);
        $temstr="      学员".$pxyarr[0]['puname']."等".count($pxyarr)."人，于".Xdate::_xxfDateToChinese($pxbrow['stime'])."至".Xdate::_xxfDateToChinese($pxbrow['etime'])."在本校参加".$pxbrow['pname']."学习，学制".Xdate::BetweenTwoDate($pxbrow['stime'], $pxbrow['etime'])."天，学习期满，经考试、考核，成绩合格。请予验印。";
        $es8->setCellValue('A4',$temstr);
        $es8->setCellValue('E15',count($pxyarr));
        $es8->setCellValue('H15',count($pxyarr));
        $temstr="      经审查".$pxyarr[0]['puname']."等".count($pxyarr)."人符合煤矿安全合格证培训班要求，同意验印。";
        $es8->setCellValue('C21',$temstr);
        //第十张考核登记表操作
        
        //$pban=xdb::getInstance()->getrow("select * from peixunban where id=".$pid);
        //$es9=$excel->getSheet(9);
        //$es9=$this->khtable($es9, $img, $pban,$pid);
        //创建Excel输入对象
        $write = new PHPExcel_Writer_Excel5($excel);
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="'.$pxbrow['pname'].'档案表.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
    }
    
    function down2fuxun($pid,$pxbrow){
        $pxyarr=xdb::getInstance()->getarray("select * from peixunxueyuan where pid=".$pid);
        $pinfo=xdb::getInstance()->getarray("select *,count(1) as knum from peixun_jk as jk,peixunkecheng as k,peixunjiaoshi as j where jk.pjid=j.id and jk.pkid=k.id and k.ktype like '普通课' and pid=".$pid." GROUP BY kname");
        //
        header("Content-Type:text/html;charset=utf-8");error_reporting(E_ALL);ini_set('display_errors', TRUE);ini_set('display_startup_errors', TRUE);
        $e5=new PHPExcel_Reader_Excel5();
        $excel=$e5->load('uploader/moban/BanJiDangAnBiaofuxun.xls');
        //第一张表单设置
        $es0=$excel->getSheet(0);
        $es0->setCellValue('B3',$pxbrow['pname']);
        $es0->setCellValue('B4',count($pxyarr).'人');
        $es0->setCellValue('B5',$pxbrow['stime'].'至'.$pxbrow['etime']);
        //第二张表单设置
        $es1=$excel->getSheet(1);
        $es1->setCellValue('B3',$pxbrow['pname']);
        $es1->setCellValue('B4',count($pxyarr).'人');
        $es1->setCellValue('B5',$pxbrow['stime'].'至'.$pxbrow['etime']);
        //第四张表单设置
        $es3=$excel->getSheet(3);
        $es3->setCellValue('A2',"培训学校：（公章）   培训班名称：".$pxbrow['pname']."     起止时间：".$pxbrow['stime'].'至'.$pxbrow['etime']);
        $i=0;
        foreach ($pxyarr as $row){
            $es3->setCellValue('A'.($i+4),$i+1);
            $es3->setCellValue('B'.($i+4),$row['idcard']);
            $es3->setCellValue('C'.($i+4),$row['puname']);
            $es3->setCellValue('D'.($i+4),_getSexFromIdcard($row['idcard']));
            $es3->setCellValue('E'.($i+4),substr(_getShengRiFromIdcard($row['idcard']),0,6)." ");
            $es3->setCellValue('F'.($i+4),$row['zhiwu']);
            $es3->setCellValue('G'.($i+4),($row['zhicheng']=="无"?"":$row['zhicheng']));
            $es3->setCellValue('H'.($i+4),$row['zhuanye']);
            $es3->setCellValue('I'.($i+4),$row['mphone']);
            $es3->setCellValue('J'.($i+4),$row['wenhua']);
            $es3->setCellValue('K'.($i+4),$row['workunit']);
            $i++;
        }
        $es3->getStyle('A4:L'.($i+3))->applyFromArray(//为新增的学员行添加边框
            array(
                'borders'=>array(
                    'allborders'=>array(
                        'style'=>PHPExcel_Style_Border::BORDER_THIN
                    )
                )
            )
            );
        //第五章表的操作
        $es4=$excel->getSheet(4);
        $es4->setCellValue('A2',"培训学校：（公章）    培训班名称：".$pxbrow['pname']."    起止时间：".$pxbrow['stime'].'至'.$pxbrow['etime']);
        $i=0;
        foreach ($pinfo as $row){$i++;
        $es4->setCellValue('A'.($i+4),$i);
        $es4->setCellValue('B'.($i+4),$row['kname']);
        $es4->setCellValue('C'.($i+4),intval($row['knum'])*2);
        $es4->setCellValue('D'.($i+4),$row['jiaocai']);
        $es4->setCellValue('E'.($i+4),$row['tname']);
        $es4->setCellValue('F'.($i+4),$row['workunit']);
        $es4->setCellValue('G'.($i+4),$row['biyeschool']);
        $es4->setCellValue('H'.($i+4),$row['zhuanye']);
        $es4->setCellValue('I'.($i+4),$row['zhicheng']);
        $es4->getRowDimension($i+4)->setRowHeight(28);
        }
        $es4->getStyle('A5:J'.($i+4))->applyFromArray(//添加边框
            array(
                'borders'=>array(
                    'allborders'=>array(
                        'style'=>PHPExcel_Style_Border::BORDER_THIN
                    )
                )
            )
            );
        //第六张表的操作考试成绩
        $es5=$excel->getSheet(5);
        $es5->setCellValue('A2',"培训学校：（公章）    培训班名称：".$pxbrow['pname']."    起止时间：".$pxbrow['stime'].'至'.$pxbrow['etime']);
        $i=0;
        foreach ($pxyarr as $row){$i++;
        $es5->setCellValue('A'.($i+3),$i);
        $es5->setCellValue('B'.($i+3),$row['puname']);
        $es5->setCellValue('C'.($i+3),$row['chengji']);
        $es5->setCellValue('D'.($i+3),$row['bukaochengji']);
        $es5->setCellValue('E'.($i+3),$row['beizhu']=="无"?"":$row['beizhu']);
        }
        $es5->getStyle('A3:E'.($i+3))->applyFromArray(//添加边框
            array(
                'borders'=>array(
                    'allborders'=>array(
                        'style'=>PHPExcel_Style_Border::BORDER_THIN
                    )
                )
            )
            );
        //第七张的操作报审表1
        $es6=$excel->getSheet(6);
        $zongkeshi=0;
        $i=0;
        foreach ($pinfo as $row){$i++;
        //$zongkeshi+=intval($row['keshi']);
        $es6->setCellValue('I'.($i+3),$row['riqi']);
        $es6->setCellValue('J'.($i+3),$row['kname']);
        $es6->setCellValue('K'.($i+3),intval($row['knum'])*2);
        $es6->setCellValue('L'.($i+3),$row['tname']);
        $zongkeshi+=intval($row['knum'])*2;
        }
        $es6->setCellValue('B2',$pxbrow['pname']);
        $es6->setCellValue('E2',xdb::getInstance()->getvar("select realname from user where id=".$pxbrow['bzrid']));
        $es6->setCellValue('B3',$pxbrow['peixunaddress']);
        $es6->setCellValue('D3',$pxbrow['dianhua']);
        $es6->setCellValue('F3',$pxbrow['youbian']);
        $es6->setCellValue('B4',$pxbrow['stime']."至".$pxbrow['etime']);
        $es6->setCellValue('B5',$zongkeshi."学时");
        $es6->setCellValue('E5',$zongkeshi."学时");
        $es6->setCellValue('G5',count($pxyarr));
        $es6->setCellValue('B7',$pxbrow['etime']);
        $es6->setCellValue('D9',$pxbrow['lilunaddress']);
        $es6->setCellValue('K17',$zongkeshi);
        //第八张表操作 报审表2
        $es7=$excel->getSheet(7);
        $ts=xdb::getInstance()->getarray("select DISTINCT tname,sex,shengri,wenhua,zhuanyeage,zhicheng,jiaoxueage,workunit from peixun_jk as jk,peixunkecheng as k,peixunjiaoshi as j where jk.pjid=j.id and jk.pkid=k.id and k.ktype like '普通课' and pid=".$pid);
        $i=0;
        foreach ($ts as $row){
            $es7->setCellValue('A'.($i+3),$row['tname']);
            $es7->setCellValue('B'.($i+3),$row['sex']);
            $es7->setCellValue('C'.($i+3),$row['shengri']);
            $es7->setCellValue('D'.($i+3),$row['wenhua']);
            $es7->setCellValue('E'.($i+3),$row['zhicheng']);
            $es7->setCellValue('F'.($i+3),$row['workunit']);
            $i++;
        }
        $es7->setCellValue('J19',$pxbrow['pname']);
        //第九张表操作 验印审批表
/*         $es8=$excel->getSheet(8);
        $temstr="      学员".$pxyarr[0]['puname']."等".count($pxyarr)."人，于".Xdate::_xxfDateToChinese($pxbrow['stime'])."至".Xdate::_xxfDateToChinese($pxbrow['etime'])."在本校参加".$pxbrow['pname']."学习，学制".Xdate::BetweenTwoDate($pxbrow['stime'], $pxbrow['etime'])."天，学习期满，经考试、考核，成绩合格。请予验印。";
        $es8->setCellValue('A4',$temstr);
        $es8->setCellValue('E15',count($pxyarr));
        $es8->setCellValue('H15',count($pxyarr));
        $temstr="      经审查".$pxyarr[0]['puname']."等".count($pxyarr)."人符合煤矿安全合格证培训班要求，同意验印。";
        $es8->setCellValue('C21',$temstr); */
        //第十张考核登记表操作
    
        //$pban=xdb::getInstance()->getrow("select * from peixunban where id=".$pid);
        //$es9=$excel->getSheet(9);
        //$es9=$this->khtable($es9, $img, $pban,$pid);
        //创建Excel输入对象
        $write = new PHPExcel_Writer_Excel5($excel);
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="'.$pxbrow['pname'].'档案表.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
    }
    
    function down2(array $xxfget){//班级档案表
        //准备需要的数据
        $pid=$xxfget['pid'];
        $pxbrow=xdb::getInstance()->getrow("select * from peixunban where id=".$pid);
        if($pxbrow['xinfuxun']=="新训"){
            $this->down2xinxun($pid,$pxbrow);
        }elseif($pxbrow['xinfuxun']=="复训"){
            $this->down2fuxun($pid,$pxbrow);
        }else{
            
        }
    }
    
    function down1(array $xxfget){//考核登记表
        $pid=$xxfget['pid'];
        header("Content-Type:text/html;charset=utf-8");error_reporting(E_ALL);ini_set('display_errors', TRUE);ini_set('display_startup_errors', TRUE);
        $excel = new PHPExcel();
        $es=$excel->getActiveSheet();
        
        $pban=xdb::getInstance()->getrow("select * from peixunban where id=".$pid);

        $es=$this->khtable($es, $pban,$pid);
        
        //创建Excel输入对象
        $write = new PHPExcel_Writer_Excel5($excel);
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.($pban['pname']).'考核登记表.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
    }
    
    function moren(array $xxfget){
        $pid=$xxfget['pid'];
        header("Content-Type:text/html;charset=utf-8");
        error_reporting(E_ALL);
        ini_set('display_errors', TRUE);
        ini_set('display_startup_errors', TRUE);
        //创建对象
        $excel = new PHPExcel();
        $excel->getActiveSheet()->setTitle('上报信息');
        //Excel表格式,这里简略写了8列
        $letter = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB');
        //表头数组
        $tableheader = array('姓名','性别','身份证件类型','身份证件号','民族','政治面貌','最高学历','毕业院校','所学专业','从事专业','健康状况','工作单位','参加工作时间','部门','职务','职称或技能等级','固定电话','移动电话','通讯地址','邮政编码','电子邮箱','任课专业','擅长领域','从事安全培训工作时间','兼职单位','培训单位','工商登记号','备注');
        //填充表头信息
        for($i = 0;$i < count($tableheader);$i++) {
            $excel->getActiveSheet()->setCellValue("$letter[$i]1","$tableheader[$i]");
        }
        $ptype=$xxfget['ptype'];
        $sqlquery="";
        switch ($ptype){
            case 1:$ptype="总表";$sqlquery="select * from peixunxueyuan where pid=".$pid;break;
            case 2:$ptype="主要负责人";$sqlquery="select * from peixunxueyuan where pid=".$pid." and ptype like '%".$ptype."%'";break;
            case 3:$ptype="安全管理人员";$sqlquery="select * from peixunxueyuan where pid=".$pid." and ptype like '%".$ptype."%'";break;
            default:;
        }
        $result=xdb::getInstance()->getarray($sqlquery);
        $letter2 = array('A','D','E','G','I','K','L','O','R','Z');
        $letterdb=array('puname','idcard','minzu','wenhua','zhuanye','jiankang','workunit','zhiwu','mphone','peixununit');
        $j=2;
        foreach ($result as $row){
            if($row){
                $excel->getActiveSheet()->setCellValue('B'.$j,_getSexFromIdcard($row['idcard']));
                $excel->getActiveSheet()->setCellValue('C'.$j,'身份证');
                for($i=0;$i<count($letter2);$i++){
                    $excel->getActiveSheet()->setCellValue($letter2[$i].$j,$row[$letterdb[$i]]);
                }
                $j++;
            }
        }
        //创建Excel输入对象
        $write = new PHPExcel_Writer_Excel5($excel);
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header('Content-Disposition:attachment;filename="'.xdb::getInstance()->getvar("select pname from peixunban where id=".$pid).$ptype.'.xls"');
        header("Content-Transfer-Encoding:binary");
        $write->save('php://output');
    }
    function xinxundown($es,$pban,$pid){//新训表下载
        $xy=xdb::getInstance()->getarray("select * from peixunxueyuan where pid=$pid");
        $kc=xdb::getInstance()->getarray("select kname,keshi from peixun_jk as jk,peixunkecheng as k where k.ktype like '普通课' and jk.pkid=k.id and jk.pid =$pid");        
        $pinfo=xdb::getInstance()->getarray("select *,count(1) as knum from peixun_jk as jk,peixunkecheng as k,peixunjiaoshi as j where jk.pjid=j.id and jk.pkid=k.id and k.ktype like '普通课' and pid=".$pid." GROUP BY kname");
        $stywidth=array(//表格宽度设置
            'A'=>10,'B'=>10,'C'=>10,'D'=>10,'E'=>10,'F'=>10,'G'=>13
        );
        foreach ($stywidth as $k=>$v){
            $es->getColumnDimension($k)->setWidth($v);
        }
        $localval=array(
            '安全合格证培训登记表（新训）','姓名','政治面貌','工作单位','民族',"培\n训\n课\n程","培训期间\n受何奖励","培训时间","培训机构\n意见",'备注',
            '合计','培训合格，准予结业。',
            '性别','职称','文化程度',
            '出生年月','现任职务','专业','总评成绩'
        );
        $n=0;//每个人的起始位置，从0开始算第一个人
        foreach ($xy as $row){
            $img = new PHPExcel_Worksheet_Drawing();
            //需要合并的单元格
            $hebing=array(
                'A'.($n+1).':G'.($n+1),
                'B'.($n+4).':F'.($n+4),
                'G'.($n+2).':G'.($n+5),
                'A'.($n+6).':A'.($n+19),
                'B'.($n+6).':F'.($n+6),
                'B'.($n+7).':F'.($n+7),
                'B'.($n+8).':F'.($n+8),
                'B'.($n+9).':F'.($n+9),
                'B'.($n+10).':F'.($n+10),
                'B'.($n+11).':F'.($n+11),
                'B'.($n+12).':F'.($n+12),
                'B'.($n+13).':F'.($n+13),
                'B'.($n+14).':F'.($n+14),
                'B'.($n+15).':F'.($n+15),
                'B'.($n+16).':F'.($n+16),
                'B'.($n+17).':F'.($n+17),
                'B'.($n+18).':F'.($n+18),
                'B'.($n+19).':F'.($n+19),
                'B'.($n+20).':G'.($n+20),
                'B'.($n+21).':D'.($n+21),
                'F'.($n+21).':G'.($n+21),
                'B'.($n+22).':G'.($n+22),
                'B'.($n+23).':G'.($n+23)
            );
            for($i=0;$i<count($hebing);$i++){
                $es->mergeCells($hebing[$i]);
            }
            //表格固定标题
            $localcel=array(
                'A'.($n+1),'A'.($n+2),'A'.($n+3),'A'.($n+4),'A'.($n+5),'A'.($n+6),'A'.($n+20),'A'.($n+21),'A'.($n+22),'A'.($n+23),
                'B'.($n+19),'B'.($n+22),
                'C'.($n+2),'C'.($n+3),'C'.($n+5),
                'E'.($n+2),'E'.($n+3),'E'.($n+5),'E'.($n+21)
            );
            for($i=0;$i<count($localcel);$i++){
                $es->setCellValue($localcel[$i],$localval[$i]);
                $es->getStyle($localcel[$i])->getFont()->setBold(true);
            }
            //图片
            $picpath='uploader/xueyuanimg/'.$row['idcard'].'.jpg';
            if(file_exists($picpath)){
                $img->setPath('uploader/xueyuanimg/'.$row['idcard'].'.jpg');
                $img->setCoordinates('G'.($n+2));
                $img->setOffsetX(5);
                $img->setOffsetY(2);
                $img->setWorksheet($es);
            }
            //从数据库中添加信息
            $dbcel=array(
                'B'.($n+2),'B'.($n+3),'B'.($n+4),'B'.($n+5),
                //'B'.($n+6),'B'.($n+7),'B'.($n+8),'B'.($n+9),'B'.($n+10),'B'.($n+11),'B'.($n+12),'B'.($n+13),'B'.($n+14),'B'.($n+15),'B'.($n+16),'B'.($n+17),'B'.($n+18),
                'B'.($n+21),'B'.($n+23),
                'D'.($n+2),'D'.($n+3),'D'.($n+5),
                'F'.($n+2),'F'.($n+3),'F'.($n+5),'F'.($n+21)
            );
            $dbval=array(
                $row['puname'],$row['zhengzhi'],$row['workunit'],$row['minzu'],
                ////////////////////////////
                //"煤矿安全生产法律法规","煤矿机电运输提升安全","煤矿开采安全","煤矿安全生产管理","煤矿爆破安全","煤矿事故应急管理","职业病防治法律法规及基础知识","“一通三防”技术及职业卫生管理","煤矿职业危害防治技术","煤矿地质与安全","事故案例分析","复习","考试",
                ////////////////////////////
                $pban['stime']."至".$pban['etime'],($row['beizhu']=='无'?"":$row['beizhu']),
                _getSexFromIdcard($row['idcard']),$row['zhicheng'],$row['wenhua'],
                _getShengRiFromIdcard($row['idcard']),$row['zhiwu'],$row['zhuanye'],($row['bukaochengji']==''?$row['chengji']:$row['bukaochengji'])
            );
            for($i=0;$i<count($dbcel);$i++){
                $es->setCellValue($dbcel[$i],$dbval[$i]);
            }
            //添加课程
            $kccel=array(
                'B'.($n+6),'B'.($n+7),'B'.($n+8),'B'.($n+9),'B'.($n+10),'B'.($n+11),'B'.($n+12),'B'.($n+113),'B'.($n+14),'B'.($n+15),'B'.($n+16),'B'.($n+17),'B'.($n+18)
            );
            $kccelG=array(
                'G'.($n+6),'G'.($n+7),'G'.($n+8),'G'.($n+9),'G'.($n+10),'G'.($n+11),'G'.($n+12),'G'.($n+113),'G'.($n+14),'G'.($n+15),'G'.($n+16),'G'.($n+17),'G'.($n+18)
            );
            $kci=count($kccel)>count($pinfo)?count($pinfo):count($kccel);
            $zongkeshi=0;
            for ($i=0;$i<$kci;$i++){
                $es->setCellValue($kccel[$i],$pinfo[$i]['kname']);
                $es->setCellValue($kccelG[$i],(intval($pinfo[$i]['knum'])*2)."课时");
                $zongkeshi+=intval($pinfo[$i]['knum'])*2;
            }
            //总计课时
            $es->setCellValue('G'.($n+19),$zongkeshi."课时");
            //设置格式
            $stycel=array(//全局设置
                'A'.($n+1),'A'.($n+2),'A'.($n+3),'A'.($n+4),'A'.($n+5),'A'.($n+6),'A'.($n+20),'A'.($n+21),'A'.($n+22),'A'.($n+23),
                'B'.($n+2),'B'.($n+3),'B'.($n+4),'B'.($n+5),'B'.($n+19),'B'.($n+20),'B'.($n+21),'B'.($n+22),'B'.($n+23),
                'C'.($n+2),'C'.($n+3),'C'.($n+5),
                'D'.($n+2),'D'.($n+3),'D'.($n+5),
                'E'.($n+2),'E'.($n+3),'E'.($n+5),'E'.($n+21),
                'F'.($n+2),'F'.($n+3),'F'.($n+5),'F'.($n+21),
                'G'.($n+2)
            );
            for($i=0;$i<count($stycel);$i++){
                $es->getStyle($stycel[$i])->applyFromArray(
                    array(
                        'alignment'=>array(
                            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'wrap'  => TRUE
                        )
                    )
                    );
            }
            //全部加上边框
            $es->getStyle('A'.($n+2).':G'.($n+23))->applyFromArray(
                array(
                    'borders'=>array(
                        'allborders'=>array(
                            'style'=>PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
                );
/*             $es->getStyle('B'.($n+13).':J'.($n+18))->applyFromArray(
                array(
                    'borders'=>array(
                        'allborders'=>array(
                            'style'=>PHPExcel_Style_Border::BORDER_NONE
                        ),
                        'right'=>array(
                            'style'=>PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
                );
            $es->getStyle('B'.($n+15).':J'.($n+15))->applyFromArray(
                array(
                    'borders'=>array(
                        'bottom'=>array(
                            'style'=>PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
                ); */
            //每一行的高度
            $es->getDefaultRowDimension()->setRowHeight(22);
            $styheight=array(
                $n+1=>40,
                $n+20=>40,
                $n+22=>40
            );
            foreach ($styheight as $k=>$v){
                $es->getRowDimension($k)->setRowHeight($v);
            }
            //特殊单元格的处理
            $es->getStyle('A'.($n+1))->getFont()->setSize(20);
//             $es->getStyle('B'.($n+14).':B'.($n+15))->applyFromArray(
//                 array(
//                     'alignment'=>array(
//                         'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_RIGHT
//                     ),
//                     'font'=>array(
//                         'size'=>11
//                     )
//                 )
//                 );
//             $es->getStyle('B'.($n+17).':B'.($n+18))->applyFromArray(
//                 array(
//                     'alignment'=>array(
//                         'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_RIGHT
//                     ),
//                     'font'=>array(
//                         'size'=>11
//                     )
//                 )
//                 );
            //下一个人的模板位置
            $n=$n+24;
        }
        return $es;
    }
    
    function fuxundown($es,$pban,$pid){//复训考核登记表
        $xy=xdb::getInstance()->getarray("select * from peixunxueyuan where pid=$pid");
        $kc=xdb::getInstance()->getarray("select kname,keshi from peixun_jk as jk,peixunkecheng as k where k.ktype like '普通课' and jk.pkid=k.id and jk.pid =$pid");
        $pinfo=xdb::getInstance()->getarray("select *,count(1) as knum from peixun_jk as jk,peixunkecheng as k,peixunjiaoshi as j where jk.pjid=j.id and jk.pkid=k.id and k.ktype like '普通课' and pid=".$pid." GROUP BY kname");
        $stywidth=array(//表格宽度设置
            'A'=>10,'B'=>10,'C'=>10,'D'=>10,'E'=>10,'F'=>10,'G'=>13
        );
        foreach ($stywidth as $k=>$v){
            $es->getColumnDimension($k)->setWidth($v);
        }
        $localval=array(
            '安全合格证培训登记表（每年再培训）','姓名','政治面貌','工作单位','民族',"培\n训\n课\n程","培训期间\n受何奖励","培训时间","培训机构\n意见",'备注',
            '合计','培训合格，准予结业。',
            '性别','职称','文化程度',
            '出生年月','现任职务','专业','总评成绩'
        );
        $n=0;//每个人的起始位置，从0开始算第一个人
        foreach ($xy as $row){
            $img = new PHPExcel_Worksheet_Drawing();
            //需要合并的单元格
            $hebing=array(
                'A'.($n+1).':G'.($n+1),
                'B'.($n+4).':F'.($n+4),
                'G'.($n+2).':G'.($n+5),
                'A'.($n+6).':A'.($n+19),
                'B'.($n+6).':F'.($n+6),
                'B'.($n+7).':F'.($n+7),
                'B'.($n+8).':F'.($n+8),
                'B'.($n+9).':F'.($n+9),
                'B'.($n+10).':F'.($n+10),
                'B'.($n+11).':F'.($n+11),
                'B'.($n+12).':F'.($n+12),
                'B'.($n+13).':F'.($n+13),
                'B'.($n+14).':F'.($n+14),
                'B'.($n+15).':F'.($n+15),
                'B'.($n+16).':F'.($n+16),
                'B'.($n+17).':F'.($n+17),
                'B'.($n+18).':F'.($n+18),
                'B'.($n+19).':F'.($n+19),
                'B'.($n+20).':G'.($n+20),
                'B'.($n+21).':D'.($n+21),
                'F'.($n+21).':G'.($n+21),
                'B'.($n+22).':G'.($n+22),
                'B'.($n+23).':G'.($n+23)
            );
            for($i=0;$i<count($hebing);$i++){
                $es->mergeCells($hebing[$i]);
            }
            //表格固定标题
            $localcel=array(
                'A'.($n+1),'A'.($n+2),'A'.($n+3),'A'.($n+4),'A'.($n+5),'A'.($n+6),'A'.($n+20),'A'.($n+21),'A'.($n+22),'A'.($n+23),
                'B'.($n+19),'B'.($n+22),
                'C'.($n+2),'C'.($n+3),'C'.($n+5),
                'E'.($n+2),'E'.($n+3),'E'.($n+5),'E'.($n+21)
            );
            for($i=0;$i<count($localcel);$i++){
                $es->setCellValue($localcel[$i],$localval[$i]);
                $es->getStyle($localcel[$i])->getFont()->setBold(true);
            }
            //图片
            $picpath='uploader/xueyuanimg/'.$row['idcard'].'.jpg';
            if(file_exists($picpath)){
                $img->setPath('uploader/xueyuanimg/'.$row['idcard'].'.jpg');
                $img->setCoordinates('G'.($n+2));
                $img->setOffsetX(5);
                $img->setOffsetY(2);
                $img->setWorksheet($es);
            }
            //从数据库中添加信息
            $dbcel=array(
                'B'.($n+2),'B'.($n+3),'B'.($n+4),'B'.($n+5),
                //'B'.($n+6),'B'.($n+7),'B'.($n+8),'B'.($n+9),'B'.($n+10),'B'.($n+11),'B'.($n+12),'B'.($n+13),'B'.($n+14),'B'.($n+15),'B'.($n+16),'B'.($n+17),'B'.($n+18),
                'B'.($n+21),'B'.($n+23),
                'D'.($n+2),'D'.($n+3),'D'.($n+5),
                'F'.($n+2),'F'.($n+3),'F'.($n+5),'F'.($n+21)
            );
            $dbval=array(
                $row['puname'],$row['zhengzhi'],$row['workunit'],$row['minzu'],
                ////////////////////////////
                //"煤矿安全生产法律法规","煤矿机电运输提升安全","煤矿开采安全","煤矿安全生产管理","煤矿爆破安全","煤矿事故应急管理","职业病防治法律法规及基础知识","“一通三防”技术及职业卫生管理","煤矿职业危害防治技术","煤矿地质与安全","事故案例分析","复习","考试",
                ////////////////////////////
                $pban['stime']."至".$pban['etime'],($row['beizhu']=='无'?"":$row['beizhu']),
                _getSexFromIdcard($row['idcard']),$row['zhicheng'],$row['wenhua'],
                _getShengRiFromIdcard($row['idcard']),$row['zhiwu'],$row['zhuanye'],($row['bukaochengji']==''?$row['chengji']:$row['bukaochengji'])
            );
            for($i=0;$i<count($dbcel);$i++){
                $es->setCellValue($dbcel[$i],$dbval[$i]);
            }
            //添加课程
            $kccel=array(
                'B'.($n+6),'B'.($n+7),'B'.($n+8),'B'.($n+9),'B'.($n+10),'B'.($n+11),'B'.($n+12),'B'.($n+113),'B'.($n+14),'B'.($n+15),'B'.($n+16),'B'.($n+17),'B'.($n+18)
            );
            $kccelG=array(
                'G'.($n+6),'G'.($n+7),'G'.($n+8),'G'.($n+9),'G'.($n+10),'G'.($n+11),'G'.($n+12),'G'.($n+113),'G'.($n+14),'G'.($n+15),'G'.($n+16),'G'.($n+17),'G'.($n+18)
            );
            $kci=count($kccel)>count($pinfo)?count($pinfo):count($kccel);
            $zongkeshi=0;
            for ($i=0;$i<$kci;$i++){
                $es->setCellValue($kccel[$i],$pinfo[$i]['kname']);
                $es->setCellValue($kccelG[$i],(intval($pinfo[$i]['knum'])*2)."课时");
                $zongkeshi+=intval($pinfo[$i]['knum'])*2;
            }
            //总计课时
            $es->setCellValue('G'.($n+19),$zongkeshi."课时");
            //设置格式
            $stycel=array(//全局设置
                'A'.($n+1),'A'.($n+2),'A'.($n+3),'A'.($n+4),'A'.($n+5),'A'.($n+6),'A'.($n+20),'A'.($n+21),'A'.($n+22),'A'.($n+23),
                'B'.($n+2),'B'.($n+3),'B'.($n+4),'B'.($n+5),'B'.($n+19),'B'.($n+20),'B'.($n+21),'B'.($n+22),'B'.($n+23),
                'C'.($n+2),'C'.($n+3),'C'.($n+5),
                'D'.($n+2),'D'.($n+3),'D'.($n+5),
                'E'.($n+2),'E'.($n+3),'E'.($n+5),'E'.($n+21),
                'F'.($n+2),'F'.($n+3),'F'.($n+5),'F'.($n+21),
                'G'.($n+2)
            );
            for($i=0;$i<count($stycel);$i++){
                $es->getStyle($stycel[$i])->applyFromArray(
                    array(
                        'alignment'=>array(
                            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical'  => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            'wrap'  => TRUE
                        )
                    )
                    );
            }
            //全部加上边框
            $es->getStyle('A'.($n+2).':G'.($n+23))->applyFromArray(
                array(
                    'borders'=>array(
                        'allborders'=>array(
                            'style'=>PHPExcel_Style_Border::BORDER_THIN
                        )
                    )
                )
                );
            /*             $es->getStyle('B'.($n+13).':J'.($n+18))->applyFromArray(
             array(
             'borders'=>array(
             'allborders'=>array(
             'style'=>PHPExcel_Style_Border::BORDER_NONE
             ),
             'right'=>array(
             'style'=>PHPExcel_Style_Border::BORDER_THIN
             )
             )
             )
             );
            $es->getStyle('B'.($n+15).':J'.($n+15))->applyFromArray(
            array(
            'borders'=>array(
            'bottom'=>array(
            'style'=>PHPExcel_Style_Border::BORDER_THIN
            )
            )
            )
            ); */
            //每一行的高度
            $es->getDefaultRowDimension()->setRowHeight(22);
            $styheight=array(
                $n+1=>40,
                $n+20=>40,
                $n+22=>40
            );
            foreach ($styheight as $k=>$v){
                $es->getRowDimension($k)->setRowHeight($v);
            }
            //特殊单元格的处理
            $es->getStyle('A'.($n+1))->getFont()->setSize(20);
            //             $es->getStyle('B'.($n+14).':B'.($n+15))->applyFromArray(
            //                 array(
            //                     'alignment'=>array(
            //                         'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_RIGHT
            //                     ),
            //                     'font'=>array(
            //                         'size'=>11
            //                     )
            //                 )
            //                 );
            //             $es->getStyle('B'.($n+17).':B'.($n+18))->applyFromArray(
            //                 array(
            //                     'alignment'=>array(
            //                         'horizontal'=>PHPExcel_Style_Alignment::HORIZONTAL_RIGHT
            //                     ),
            //                     'font'=>array(
            //                         'size'=>11
            //                     )
            //                 )
            //                 );
            //下一个人的模板位置
            $n=$n+24;
        }
        return $es;
    }
    function khtable($es,$pban,$pid){
        if($pban['xinfuxun']=="新训"){
            return $this->xinxundown($es,$pban,$pid);
        }elseif($pban['xinfuxun']=="复训"){
            return $this->fuxundown($es,$pban,$pid);
        }else{
            
        }
    }
}